# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
import sys
from xlutils.copy import copy
import os
import datetime
import math

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function: 数据初始化对比：cvs1.0与cvs2.0 企业列表对比（企业名称、成立时间、详细地址、联系电话、企业网址、
                                                        当前轮次、融资金额、当前估值、本轮投资方名称、本轮投资金额、
                                                        人物名称、职位）
企业网址、当前估值 在view_company;其他字段在search_company里
人物名称、职位 在天眼查，先不用对比
当前估值 在1.0里面是没有的，2.0中新计算的值，所以也不用对比
"""

def searchCvs1CompanyList():
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM dc_company a,dc_event b,dc_enterprise c " \
          "WHERE a.`status`='1' AND b.enterprise_id = c.id AND c.company_id = a.id ORDER BY a.id DESC"
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50

    # for index in range(pageCount):
    for index in range(2,3531):
    #上次0723号跑到7650条
        limitStart = index * 50

        print("limitStart====================================================",limitStart)

        sql = "SELECT temp.id, temp.companyName, temp.begin_time, temp.address, '' AS invest_round, '' AS financingAmount, " \
              "'' AS invest_data, '' AS telephone, '' AS description, temp.website, temp.beizhu " \
              "FROM" \
              " ( " \
              "SELECT IFNULL(a.cn_name, a.en_name) AS companyName, a.id, a.begin_time, a.reg_location AS address, " \
              "a.web_url AS website, a.ext1 AS beizhu " \
              "FROM dc_company a, dc_event b, dc_enterprise c " \
              "WHERE a.`status` = '1' AND b.enterprise_id = c.id AND c.company_id = a.id) temp " \
              "ORDER BY temp.companyName DESC LIMIT %d,50 "%limitStart

        # sql = "SELECT temp.id, temp.companyName, temp.begin_time, temp.address, '' AS invest_round, '' AS financingAmount, " \
        #       "'' AS invest_data, '' AS telephone, '' AS description, temp.website, temp.beizhu " \
        #       "FROM " \
        #       "( SELECT IFNULL(a.cn_name, a.en_name) AS companyName, a.id, a.begin_time, a.reg_location AS address, " \
        #       "a.web_url AS website, a.ext1 AS beizhu " \
        #       "FROM datacenter_online_20180609.dc_company a, dc_event b, dc_enterprise c " \
        #       "WHERE " \
        #       "a.`status` = '1' AND b.enterprise_id = c.id AND c.company_id = a.id AND a.cn_name = '黑石集团' LIMIT 1 ) temp"

        cursor.execute(sql)
        companyResults1 = cursor.fetchall()
        fields = cursor.description
        #对比企业信息（成立时间、企业网址、详细地址、联系电话、企业网址、当前轮次、融资金额、当前估值、本轮投资方名称、本来投资金额）
        compareCvs2CompanyList(companyResults1, fields)

    db.close()


def compareCvs2CompanyList(companyResults1, fields):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cvs_prod", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    companyNameStr = ''
    for index in range(len(companyResults1)):
        print(index)
        companyName1 = companyResults1[index].get(u"companyName")
        if companyName1 != None:
            companyName1 = MySQLdb.escape_string(companyName1)
            companyNameStr += ",'"+companyName1+"'"
    print(companyNameStr)
    companyNameStr=companyNameStr[1:]

    #financingAmount--融资金额  invest_data--存投资信息包括投资公司名称和投资金额，存的是json
    viewCompanySql = "SELECT company_id AS id,full_name AS companyName,begin_time,contact_address_data  AS address,invest_round," \
                  "financing_amount AS financingAmount, invest_data,contact_data AS telephone,description,website, enable_search AS beizhu " \
                  "FROM search_company " \
                  "WHERE full_name IN (%s) " \
                  "ORDER BY full_name DESC"%companyNameStr

    cursor.execute(viewCompanySql)
    viewCompanyResults2 = cursor.fetchall()
    for index in range(len(companyResults1)):
        companyName1 = companyResults1[index].get(u"companyName")
        beginTime1 = companyResults1[index].get(u"begin_time")
        if beginTime1 != None and beginTime1 !="":
            beginTime1 = datetime.datetime.strftime(beginTime1,'%Y-%m-%d')
        website1 = companyResults1[index].get(u"website")
        #1.0库中注册地址
        regAddress1 = companyResults1[index].get(u"address")
        companyId1 = companyResults1[index].get(u"id")

        for viewCompanyIndex in range(len(viewCompanyResults2)):
            companyName2= viewCompanyResults2[viewCompanyIndex].get(u"companyName")
            if companyName2 == companyName1:
                begin_time2 = viewCompanyResults2[viewCompanyIndex].get(u"begin_time")
                if begin_time2 != None and begin_time2 !="":
                    begin_time2 = datetime.datetime.strftime(begin_time2,'%Y-%m-%d')
                web_url2 = viewCompanyResults2[viewCompanyIndex].get(u"website")
                invest_round2 = viewCompanyResults2[viewCompanyIndex].get(u"invest_round")
                financing_amount2 = viewCompanyResults2[viewCompanyIndex].get(u"financingAmount")
                #存投资信息包括投资公司名称和投资金额，存的是json
                invest_data2 = viewCompanyResults2[viewCompanyIndex].get(u"invest_data")
                #2.0中注册地址和联系地址
                address2 = viewCompanyResults2[viewCompanyIndex].get(u"address")
                #2.0库中联系电话
                telephone2 = viewCompanyResults2[viewCompanyIndex].get(u"telephone")

                if beginTime1 != begin_time2:
                     beizhuStr1 = u'cvs1和2中search_company 成立时间 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中search_company 成立时间 不完全匹配，此为cvs2中数据'
                     writeData(companyResults1[index], fields, beizhuStr1, viewCompanyResults2[viewCompanyIndex], beizhuStr2)

                #判断企业网址是否一致，企业网址 和 当前估值 保存在view_company表，其他信息保存在search_company表
                #新加注释，“当前估值”是2.0新加的字段，1.0中没有，所以不用对比了
                compareCvs1and2Website(companyResults1[index], fields, website1, companyName1)

                #判断‘详细地址’ 是否一致
                isAddressBreak = compareAddress(companyResults1[index], fields, regAddress1, viewCompanyResults2[viewCompanyIndex], address2, companyId1)

                #判断‘联系电话’ 是否一致  '2'代表判断联系电话
                isTelephoneBreak =compareCvs1and2CompanyContact(companyResults1[index], fields, companyId1, telephone2,viewCompanyResults2[viewCompanyIndex],'2')

                #判断“企业投融资信息”
                compareCvs2CompanyFinancingList(companyResults1[index], fields,invest_round2,financing_amount2,invest_data2)

            else:
                continue

    db.close()

def compareCvs1and2Website(companyResults1, fields, website1, companyName1):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cms_plus", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT website FROM view_company a " \
          "WHERE full_name='%s'" % companyName1

    cursor.execute(sql)
    viewCompanyResults2 = cursor.fetchall()

    for index in range(len(viewCompanyResults2)):
        website2 = viewCompanyResults2[index].get(u"website")
        #如果1.0网址不为空，则去判断2.0中网址是否与1.0一致
        if website1 != None and website1 != "":
            if website2 == None and website1 == "":
                beizhuStr1 = u'cvs2中此企业缺失此网址信息，此为cvs1中数据：'+website1
                writeDataOne(companyResults1, fields, beizhuStr1)
            else:
                if website1 != website2:
                    beizhuStr1 = u'cvs1和2中 view_company 企业网址 不完全匹配，此为cvs1中数据：'+website1 +"||此为cvs2中 企业网址 数据："+website2
                    writeDataOne(companyResults1, fields, beizhuStr1)

#判断联系地址是否一致
def compareAddress(companyResults1, fields, regAddress1, viewCompanyResults2, address2, orgId1):
    #判断2.0库中是否包含1.0的联系地址
    isok = compareCvs1and2CompanyContact(companyResults1, fields, orgId1, address2, viewCompanyResults2, "1")
    #1.0库中联系地址有数据，但是2.0库中不包含此联系地址，则此条数据不符合条件，excel记录问题后直接跳出循环进入到下一条数据的判断。因为2.0库中view表地址取值为先取联系地址，联系地址无数据时才取注册地址，则联系地址有数据时必须先取联系地址数据
    #2.0库中不包含1.0中的联系地址
    if isok=="Addressbreak":
        return "Addressbreak"

    #如果1.0的联系地址为空，则继续判断1.0中注册地址
    elif isok == "contactAddressNull":
        if regAddress1 != None and regAddress1 !='':
            #如果1.0中有注册地址，2.0中 注册地址为空 则有问题直接记录问题数据
            if address2 == None or address2 == "[]":
                address2 = "None"
                beizhuStr1=u'cvs2中此企业缺失此条注册地址，此为cvs1中注册地址：' + regAddress1
                beizhuStr2=u'cvs2中此企业缺失此条注册地址，此为cvs2中注册地址：' + address2
                writeData(companyResults1, fields, beizhuStr1, viewCompanyResults2, beizhuStr2)
                return "Addressbreak"
            else:
                #判断2.0库中是否包含1.0的注册地址,如果不包含，直接记录问题
                isRegAddress = regAddress1 in address2
                if isRegAddress != True:
                    beizhuStr1=u'cvs2中此企业缺失此条注册地址，此为cvs1中注册地址：' + regAddress1
                    beizhuStr2=u'cvs2中此企业缺失此条注册地址，此为cvs2中注册地址：' + address2
                    writeData(companyResults1, fields, beizhuStr1, viewCompanyResults2, beizhuStr2)
                    return "Addressbreak"

#判断2.0库中是否包含1.0的联系地址 和 联系电话
def compareCvs1and2CompanyContact(companyResults1, fields, companyId1, contact_address_data2, viewCompanyResults2, type):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT b.cn_address, b.telephone " \
          "FROM dc_company a, dc_contact b " \
          "WHERE b.context_type=1 AND a.id = b.context_id AND a.`status`=1 AND a.id='%s'" % companyId1

    cursor.execute(sql)
    companyContactResults1 = cursor.fetchall()
    for index in range(len(companyContactResults1)):
        #type == 1 为判断详细地址，type==2 为判断联系电话
        if type == '1':
            contactAddress1 = companyContactResults1[index].get(u"cn_address")
            contact_address_data2 = viewCompanyResults2.get(u"address")
            #如果1.0库中联系地址有数据，判断2.0库中是否包含此联系地址
            if contactAddress1!= None:
                #如果1.0中联系地址有数据，但是2.0库中的注册和联系地址为空 则直接记录数据问题
                if contact_address_data2 == None or contact_address_data2 == "[]":
                    contact_address_data2 = "None"
                    beizhuStr1=u'cvs2中此企业缺失此条联系地址，此为cvs1中联系地址：' + contactAddress1
                    beizhuStr2=u'cvs2中此企业缺失此条联系地址，此为cvs2中联系地址：' + contact_address_data2
                    writeData(companyResults1, fields, beizhuStr1, viewCompanyResults2, beizhuStr2)
                    return "Addressbreak"
                else:
                    #判断2.0库中是否包含1.0库中的联系地址
                    isContactAddress = contactAddress1 in contact_address_data2
                    if isContactAddress == False:
                        beizhuStr1=u'cvs2中此企业缺失此条联系地址，此为cvs1中联系地址：' + contactAddress1
                        beizhuStr2=u'cvs2中此企业缺失此条联系地址，此为cvs2中联系地址：' + contact_address_data2
                        writeData(companyResults1, fields, beizhuStr1, viewCompanyResults2, beizhuStr2)
                        return "Addressbreak"
            #else如果1.0库中联系地址无数据，则看1.0中company表是否有注册地址信息，如果有注册地址信息，则2.0库中应该显示注册地址信息
            else:
                return "contactAddressNull"
        elif type == '2':
            telephone1 = companyContactResults1[index].get(u"telephone")
            telephone2 = viewCompanyResults2.get(u"telephone")
            if telephone1!= None and telephone1 !="":
                #如果1.0中联系电话有数据，但是2.0库中的联系电话为空 则直接记录数据问题
                if telephone2 == None or telephone2 == "[]":
                    telephone2 = "None"
                    beizhuStr1=u'cvs2中此企业缺失此条联系电话，此为cvs1中联系电话：' + telephone1
                    beizhuStr2=u'cvs2中此企业缺失此条联系电话，此为cvs2中联系电话：' + telephone2
                    writeData(companyResults1, fields, beizhuStr1, viewCompanyResults2, beizhuStr2)
                    return "telephoneBreak"
                else:
                    #如果1.0中联系电话有数据，同时2.0库中的联系电话不为空，判断2.0库中是否包含1.0库中的联系电话
                    isContactTelephone = telephone1 in telephone2
                    if isContactTelephone == False:
                        beizhuStr1=u'cvs2中此企业缺失此条联系电话，此为cvs1中联系电话：' + telephone1
                        beizhuStr2=u'cvs2中此企业缺失此条联系电话，此为cvs2中联系电话：' + telephone2
                        writeData(companyResults1, fields, beizhuStr1, viewCompanyResults2, beizhuStr2)
                        # writeContactTelephoneData(orgResults1, fields, telephone1, viewOrgResults2, telephone2)
                        return "telephoneBreak"
    db.close()

#判断1.0和2.0 “企业投融资信息”是否一致
def compareCvs2CompanyFinancingList(companyResults1, fields, invest_round2, financing_amount2, invest_data2):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    companyNameStr = ''
    # for index in range(len(viewCompanyResults2)):
    #     print(index)
    companyName1 = companyResults1.get(u"companyName")
        # if invest_round1 != None:
        #     invest_round1 = MySQLdb.escape_string(invest_round1)
        #     companyNameStr += ",'"+invest_round1+"'"
    # print(companyNameStr)
    # companyNameStr=companyNameStr[1:]

    #融资事件
    # p.financing_type AS invest_round --当前轮次    p.reality_amount AS financing_amount--融资金额   s.`name` AS invest_data--投资方名称
    #r.reality_amount AS invest_amount--投资金额
    companyFinancingSql = "SELECT p.id, p.financing_time, p.event_id, IFNULL(n.cn_name, n.en_name) AS companyName , " \
                  "n.begin_time, n.reg_location, n.web_url, p.financing_type AS invest_round, p.reality_amount AS financing_amount, " \
                  "r.reality_amount AS invest_amount, s.`name` AS invest_data, " \
                  "IFNULL(n.cn_short, n.en_short) AS companyShortName, r.org_id, r.fund_id, r.id AS financingInvestId " \
                  "FROM " \
                  "dc_company n, dc_event o, dc_event_financing p, dc_enterprise q, dc_event_financing_invest r, dc_organization s, " \
                  "( " \
                  "SELECT IFNULL(a.cn_name, a.en_name) AS companyName, MAX(c.financing_time) AS financing_time " \
                  "FROM dc_company a, dc_event b, dc_event_financing c, dc_enterprise d, dc_event_financing_invest e, dc_organization f " \
                  "WHERE a.id = d.company_id AND b.enterprise_id = d.id AND b.id = c.event_id AND e.financing_id = c.id AND f.id = e.org_id " \
                  "AND a.cn_name='%s' " \
                  "GROUP BY IFNULL(a.cn_name, a.en_name)) m " \
                  "WHERE n.id = q.company_id AND o.enterprise_id = q.id AND o.id = p.event_id AND r.financing_id = p.id " \
                  "AND s.id = r.org_id AND m.companyName = IFNULL(n.cn_name, n.en_name) AND p.financing_time = m.financing_time " \
                  "ORDER BY IFNULL(n.cn_name, n.en_name) DESC"%companyName1

    #上市事件
    companyIpoSql = "SELECT p.id, p.ipo_time, p.event_id, IFNULL(n.cn_name, n.en_name) AS companyName, " \
                    "n.begin_time, n.reg_location, n.web_url, p.sum_money AS financing_amount, " \
                    "IFNULL(n.cn_short, n.en_short) AS companyShortName " \
                    "FROM " \
                    "dc_company n, dc_event o, dc_event_ipo p, dc_enterprise q, " \
                    "( " \
                    "SELECT IFNULL(a.cn_name, a.en_name) AS companyName, MAX(c.ipo_time) AS ipo_time " \
                    "FROM dc_company a, dc_event b, dc_event_ipo c, dc_enterprise d " \
                    "WHERE a.id = d.company_id AND b.enterprise_id = d.id AND b.id = c.event_id AND " \
                    "a.cn_name = '%s' " \
                    "GROUP BY IFNULL(a.cn_name, a.en_name)) m " \
                    "WHERE n.id = q.company_id AND o.enterprise_id = q.id AND o.id = p.event_id AND " \
                    "m.companyName = IFNULL(n.cn_name, n.en_name) AND p.ipo_time = m.ipo_time " \
                    " ORDER BY IFNULL(n.cn_name, n.en_name) DESC"%companyName1

    #并购事件
    #p.pay_amount AS financing_amount--融资金额   r.pay_amount AS invest_amount--投资金额
    companyMergeSql = "SELECT p.id, p.end_time, p.event_id, IFNULL(n.cn_name, n.en_name) AS companyName, " \
                      "n.begin_time, n.reg_location, n.web_url, IFNULL(n.cn_short, n.en_short) AS companyShortName, " \
                      "r.pay_amount AS invest_amount, r.enterprise_id, p.pay_amount AS financing_amount " \
                      "FROM " \
                      "dc_company n, dc_event o, dc_event_merge p, dc_enterprise q, dc_event_merge_buyer r, " \
                      "( " \
                      "SELECT IFNULL(a.cn_name, a.en_name) AS companyName, MAX(c.end_time) AS end_time " \
                      "FROM dc_company a, dc_event b, dc_event_merge c, dc_enterprise d, dc_event_merge_buyer e " \
                      "WHERE a.id = d.company_id AND b.enterprise_id = d.id AND b.id = c.event_id AND e.merge_id = c.id AND " \
                      "a.cn_name = '%s' GROUP BY IFNULL(a.cn_name, a.en_name)) m " \
                      "WHERE n.id = q.company_id AND o.enterprise_id = q.id AND o.id = p.event_id AND r.merge_id = p.id " \
                      "AND m.companyName = IFNULL(n.cn_name, n.en_name) AND p.end_time = m.end_time " \
                      "ORDER BY IFNULL(n.cn_name, n.en_name) DESC"%companyName1

    cursor.execute(companyFinancingSql)
    companyFinancingResults1 = cursor.fetchall()
    cursor.execute(companyIpoSql)
    companyIpoResults1 = cursor.fetchall()
    cursor.execute(companyMergeSql)
    companyMergeResults1 = cursor.fetchall()

    ipo_time1 = ""
    financing_time1 = ""
    end_time1 = ""
    if len(companyIpoResults1)>0:
        ipo_time1 = companyIpoResults1[0].get(u"ipo_time")
    if len(companyMergeResults1)>0:
        end_time1 = companyMergeResults1[0].get(u"end_time")
    if len(companyFinancingResults1)>0:
        financing_time1 = companyFinancingResults1[0].get(u"financing_time")

    #1代表融资事件；2代表并购事件；3代表上市事件
    flagTime = 0
    if (financing_time1 != None and financing_time1 != "") and (ipo_time1 != None and ipo_time1 != ""):
        if financing_time1 > ipo_time1:
            if end_time1 != None and end_time1 != "":
                if financing_time1 > end_time1:
                    flagTime = 1
                    return flagTime
                    print("financing_time1是最大值")
                else:
                    print("end_time1是最大值")
                    flagTime = 2
                    return flagTime
    if (financing_time1 != None and financing_time1 != "") and (end_time1 != None and end_time1 != ""):
        if financing_time1 > end_time1:
            if ipo_time1 != None and ipo_time1 != "":
                if financing_time1 > ipo_time1:
                    print("financing_time1是最大值")
                    flagTime = 1
                    return flagTime
                else:
                    print("ipo_time1是最大值")
                    flagTime = 3
                    return flagTime
    if (ipo_time1 != None and ipo_time1 != "") and (end_time1 != None and end_time1 != ""):
        if ipo_time1 > end_time1:
            print("ipo_time1是最大值")
            flagTime = 3
            return flagTime
        else:
            print("end_time1是最大值")
            flagTime = 2
            return flagTime
    #1代表融资事件，有投资方和投资金额
    if flagTime == 1:
        for index in range(len(companyFinancingResults1)):
            invest_round1 = companyFinancingResults1[index].get(u"invest_round")
            financing_amount1 = companyFinancingResults1[index].get(u"financing_amount")
            list.append()
            if financing_amount1 != None and financing_amount1 !="":
                financing_amount1 = financing_amount1*1000000
                financing_amount1 = str(financing_amount1)
                financing_amountstr = financing_amount1.split('.')
                financing_amount1 = financing_amountstr[0]
            invest_data1 = companyFinancingResults1[index].get(u"invest_data")
            #企业列表中“本轮投资方名称”展示机构简称+基金简称，所以此处需要获取简称做对比
            org_id1 = companyFinancingResults1[index].get(u"org_id")
            fund_id1 = companyFinancingResults1[index].get(u"fund_id")
            financingInvestId1 = companyFinancingResults1[index].get(u"financingInvestId")

            investRoundSql = "SELECT dict_name FROM dict_common " \
                "WHERE type_name='financingType' AND dict_value='%s'" % invest_round1

            cursor.execute(investRoundSql)
            investRoundResults1 = cursor.fetchall()
            if len(investRoundResults1) >0:
                investRoundName1 = investRoundResults1[0].get(u"dict_name")
                if investRoundName1 in "Growth":
                    investRoundName1 = "E轮以后"
                if investRoundName1 in "Buyout":
                    investRoundName1 = "Buyout"
                if " " in investRoundName1:
                    investRoundNameStr = investRoundName1.split(" ")
                    investRoundName1 = investRoundNameStr[1]
                if invest_round2 != None and invest_round2 !="":
                    round_name2 = compareCvs1and2InvestRound(investRoundName1,invest_round2)
                    isInvestRound = investRoundName1 in round_name2
                    if isInvestRound !=True:
                        beizhuStr1=u'cvs2中此企业投融资search_company‘当前轮次’信息与cvs1不匹配，此为cvs1中当前轮次：' + investRoundName1+"||"+"此为cvs2中当前轮次：" + round_name2
                        writeDataOne(companyResults1, fields, beizhuStr1)
                else:
                    beizhuStr1=u'cvs2中此企业投融资search_company‘当前轮次’信息缺失，此为cvs1中当前轮次：' + investRoundName1+"||"+"此为cvs2中当前轮次：None"
                    writeDataOne(companyResults1, fields, beizhuStr1)
            else:
                investRoundName1 = "战略融资"
                isInvestRound = investRoundName1 in round_name2
                if isInvestRound !=True:
                    beizhuStr1=u'cvs2中此企业投融资search_company‘当前轮次’信息与cvs1不匹配，此为cvs1中当前轮次：' + investRoundName1+"||"+"此为cvs2中当前轮次：" + round_name2
                    writeDataOne(companyResults1, fields, beizhuStr1)

            #判断 企业投融资-投资方名称 是否一致
            compareCvs1and2investCompanyName(companyResults1,fund_id1,financingInvestId1,invest_data2,fields)

            invest_amount1 = companyFinancingResults1[index].get(u"invest_amount")
            if invest_amount1 != None and invest_amount1 !="":
                invest_amount1 = invest_amount1*1000000
                invest_amount1 = str(invest_amount1)
                investstr = invest_amount1.split('.')
                invest_amount1 = investstr[0]
                isinvestAmount = invest_amount1 in invest_data2
                if isinvestAmount !=True:
                    beizhuStr1=u'cvs2中此企业投融资‘投资金额’信息与cvs1不匹配，此为cvs1中投资金额：' + invest_amount1+"||"+"此为cvs2中投资金额：" + invest_data2
                    writeDataOne(companyResults1, fields, beizhuStr1)

            if financing_amount1 != None and financing_amount1 !="":
                if financing_amount2 == None:
                    beizhuStr1=u'cvs2中此企业投融资‘融资金额’信息与cvs1不匹配，此为cvs1中融资金额：' + financing_amount1+"||"+"此为cvs2中融资金额：" + "None"
                    writeDataOne(companyResults1, fields, beizhuStr1)
                else:
                    financing_amount2 = str(financing_amount2)
                    isFinancingAmount = financing_amount1 in financing_amount2
                    if isFinancingAmount !=True:
                        beizhuStr1=u'cvs2中此企业投融资‘融资金额’信息与cvs1不匹配，此为cvs1中融资金额：' + financing_amount1+"||"+"此为cvs2中融资金额：" + financing_amount2
                        writeDataOne(companyResults1, fields, beizhuStr1)
                # companyResults1[u"invest_data"]=invest_data1

    #2代表并购事件，有买方名称（列表显示在投资放名称），投资金额
    if flagTime == 2:
        for index in range(len(companyMergeResults1)):
            #原有’并购模块“过来的会默认为”战略交易
            round1 = '战略交易'
            if invest_round2 != None and invest_round2 !="":
                round_name2 = compareCvs1and2InvestRound(invest_round2)
                isRound = round1 in round_name2
                if isRound !=True:
                    beizhuStr1=u'cvs2中此企业投融资search_company‘当前轮次’信息与cvs1不匹配，此为cvs1中当前轮次：' + round1+"||"+"此为cvs2中当前轮次：" + round_name2
                    writeDataOne(companyResults1, fields, beizhuStr1)

            #企业列表中如果是并购事件，“本轮投资方名称”就展示买方的简称，所以此处需要获取简称做对比
            buyerNameEnterpriseId1 = companyFinancingResults1[index].get(u"enterprise_id")
            #判断 企业投融资-投资方名称 是否一致
            compareCvs1and2BuyerNameCompanyName(companyResults1,buyerNameEnterpriseId1,invest_data2,fields)

            #并购事件的投资金额
            pay_amount1 = companyMergeResults1[index].get(u"invest_amount")
            # invest_amount1 = companyFinancingResults1[index].get(u"invest_amount")
            if pay_amount1 != None and pay_amount1 !="":
                pay_amount1 = pay_amount1*1000000
                pay_amount1 = str(pay_amount1)
                pay_amountStr = pay_amount1.split('.')
                pay_amount1 = pay_amountStr[0]
                isPayAmount = pay_amount1 in invest_data2
                if isPayAmount !=True:
                    beizhuStr1=u'cvs2中此企业投融资‘投资金额’信息与cvs1不匹配，此为cvs1中投资金额：' + pay_amount1+"||"+"此为cvs2中投资金额：" + invest_data2
                    writeDataOne(companyResults1, fields, beizhuStr1)

            #融资金额
            financing_amount1 = companyMergeResults1[index].get(u"financing_amount")
            if financing_amount1 != None and financing_amount1 !="":
                financing_amount1 = financing_amount1*1000000
                financing_amount1 = str(financing_amount1)
                financing_amountstr = financing_amount1.split('.')
                financing_amount1 = financing_amountstr[0]

            if financing_amount1 != None and financing_amount1 !="":
                if financing_amount2 == None:
                    beizhuStr1=u'cvs2中此企业投融资‘融资金额’信息与cvs1不匹配，此为cvs1中融资金额：' + financing_amount1+"||"+"此为cvs2中融资金额：" + "None"
                    writeDataOne(companyResults1, fields, beizhuStr1)
                else:
                    financing_amount2 = str(financing_amount2)
                    isFinancingAmount = financing_amount1 in financing_amount2
                    if isFinancingAmount !=True:
                        beizhuStr1=u'cvs2中此企业投融资‘融资金额’信息与cvs1不匹配，此为cvs1中融资金额：' + financing_amount1+"||"+"此为cvs2中融资金额：" + financing_amount2
                        writeDataOne(companyResults1, fields, beizhuStr1)

    #3代表上市事件
    if flagTime == 3:
        for index in range(len(companyIpoResults1)):
            invest_round1 = "IPO"
            if invest_round2 != None and invest_round2 !="":
                round_name2 = compareCvs1and2InvestRound(invest_round2)
                isInvestRound = investRoundName1 in round_name2
                if isInvestRound !=True:
                    beizhuStr1=u'cvs2中此企业投融资search_company‘当前轮次’信息与cvs1不匹配，此为cvs1中当前轮次：' + investRoundName1+"||"+"此为cvs2中当前轮次：" + round_name2
                    writeDataOne(companyResults1, fields, beizhuStr1)
            else:
                beizhuStr1=u'cvs2中此企业投融资search_company‘当前轮次’信息缺失，此为cvs1中当前轮次：' + investRoundName1+"||"+"此为cvs2中当前轮次：None"
                writeDataOne(companyResults1, fields, beizhuStr1)

            financing_amount1 = companyFinancingResults1[index].get(u"financing_amount")
            if financing_amount1 != None and financing_amount1 !="":
                financing_amount1 = financing_amount1*1000000
                financing_amount1 = str(financing_amount1)
                financing_amountstr = financing_amount1.split('.')
                financing_amount1 = financing_amountstr[0]
            if financing_amount1 != None and financing_amount1 !="":
                if financing_amount2 == None:
                    beizhuStr1=u'cvs2中此企业投融资‘融资金额’信息与cvs1不匹配，此为cvs1中融资金额：' + financing_amount1+"||"+"此为cvs2中融资金额：" + "None"
                    writeDataOne(companyResults1, fields, beizhuStr1)
                else:
                    financing_amount2 = str(financing_amount2)
                    isFinancingAmount = financing_amount1 in financing_amount2
                    if isFinancingAmount !=True:
                        beizhuStr1=u'cvs2中此企业投融资‘融资金额’信息与cvs1不匹配，此为cvs1中融资金额：' + financing_amount1+"||"+"此为cvs2中融资金额：" + financing_amount2
                        writeDataOne(companyResults1, fields, beizhuStr1)

    db.close()

#根据轮次id从2.0库中轮次字典表中获取轮次名称用于与1.0库中轮次名称对比
def compareCvs1and2InvestRound(invest_round2):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cms_plus", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT round_name FROM tbl_round a WHERE id='%s'" %invest_round2

    cursor.execute(sql)
    viewInvestRoundResults2 = cursor.fetchall()
    if len(viewInvestRoundResults2)>0:
        round_name2 = viewInvestRoundResults2[0].get(u"round_name")
        return round_name2

#r如果是并购事件判断企业投融资信息投资方名称简称（买方企业简称）
def compareCvs1and2BuyerNameCompanyName(companyResults1,buyerNameEnterpriseId1,invest_data2,fields):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    companyMergeSql = "SELECT IFNULL(b.cn_name, b.en_name) AS buyerName FROM dc_enterprise a,dc_company b " \
                              "WHERE a.status = '1' AND a.company_id = b.id AND a.id = '%s' "%buyerNameEnterpriseId1

    cursor.execute(companyMergeSql)
    companyMergeBuyResult1 = cursor.fetchall()
    if len(companyMergeBuyResult1) > 0:
        buyerName1 = companyMergeBuyResult1[0].get(u"buyerName")
        isbuyerNameData = buyerName1 in invest_data2
        if isbuyerNameData !=True:
            beizhuStr1=u'cvs2中此企业投融资‘投资方名称’信息与cvs1不匹配，此为cvs1中投资方名称（买方简称）：' + buyerName1+"||"+"此为cvs2中投资方名称：" + invest_data2
            writeDataOne(companyResults1, fields, beizhuStr1)

#判断企业投融资信息投资方名称简称（投资机构简称+基金）
def compareCvs1and2investCompanyName(companyResults1,fundId1,financingInvestId1,invest_data2,fields):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT IFNULL(b.cn_short, b.en_short) AS orgShortName, d.fundShortName, c.fund_id, d.id " \
          "FROM " \
          "dc_organization a, dc_company b, dc_event_financing_invest c, " \
          "( SELECT IFNULL(n.cn_name, n.en_name) AS fundShortName, m.id " \
          "FROM dc_fund m, dc_company n " \
          "WHERE m.company_id = n.id AND m.`status` = '1' " \
          "AND m.id = '%s' ) d WHERE a.company_id = b.id AND c.org_id = a.id AND d.id = c.fund_id " \
          "AND c.id = '%s'" %(fundId1,financingInvestId1)

    cursor.execute(sql)
    investShortNameResults2 = cursor.fetchall()
    if len(investShortNameResults2)>0:
        orgShortName1 = investShortNameResults2[0].get(u"orgShortName")
        #1.0中基金没有简称，2.0里面才有简称，所以此处查询就是查的基金全称
        fundShortName1 = investShortNameResults2[0].get(u"fundShortName")
        isInvestOrgData = orgShortName1 in invest_data2
        isInvestFundData = fundShortName1 in invest_data2
        if isInvestOrgData !=True or isInvestFundData != True:
            beizhuStr1=u'cvs2中此企业投融资‘投资方名称’信息与cvs1不匹配，此为cvs1中投资方名称：' + orgShortName1+"-"+fundShortName1+"||"+"此为cvs2中投资方名称：" + invest_data2
            writeDataOne(companyResults1, fields, beizhuStr1)


#判断企业核心团队是否一致
#企业的核心团队人物名称和职位 是从天眼查拿的，先不对数据呢
# def compareCvs1and2CompanyManager(companyResults1, fields, companyId1, contact_address_data2, viewCompanyResults2, type):
#     db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
#                          cursorclass=MySQLdb.cursors.DictCursor)
#     cursor = db.cursor()
#
#     sql = "SELECT personage.id, personage.cn_name, personage.en_name, b.context_id, manager.context_type, " \
#           "manager.title " \
#           "FROM" \
#           " dc_personage personage, dc_manager manager, dc_company company " \
#           "WHERE " \
#           "company.id = manager.context_id AND manager.personage_id = personage.id AND manager.context_type = 2 " \
#           "AND company.`status` = '1' AND company.id = '%s'" % companyId1
#
#     cursor.execute(sql)
#     companyManagerResults1 = cursor.fetchall()
#
#
#     db.close()

def writeData(companyResults1, fields, beizhuStr1, viewCompanyResults2, beizhuStr2):
     companyResults1.update(beizhu=beizhuStr1)
     writeExcel(companyResults1, fields)
     viewCompanyResults2.update(beizhu=beizhuStr2)
     writeExcel(viewCompanyResults2, fields)

def writeDataOne(companyResults1, fields, beizhuStr1):
     companyResults1.update(beizhu=beizhuStr1)
     writeExcel(companyResults1, fields)

def writeExcel(results,fields):

    fileName = 'dataViewCompany.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
